package cn.ainit.sys.common.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;

import javax.annotation.Resource;


import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cn.ainit.sys.constant.TypesConstant;
import cn.ainit.sys.dz.entity.SysDzSource;
import cn.ainit.sys.framework.entity.SysColumns;


@Repository
public class BusinessBaseDao  {

//	public JdbcTemplate jdbcTemplate;
//	
//	public JdbcTemplate getJdbcTemplate() {
//		return jdbcTemplate;
//	}
//	
//	@Resource
//	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
//		this.jdbcTemplate = jdbcTemplate;
//	}
	public static JdbcTemplate jdbcTemplate;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	
	@Resource
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	public SessionFactory sessionFactory;
	
	@Resource
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}
	
	public int dbType = TypesConstant.DB_ORACLE_TYPE;	
	/**
	 * 根据单据的Id查询相关的审批节点
	 * @param id
	 * @return
	 */
	public List<Object> getAproList(String id){
		String hql = "from PlanningTopicApprove p where p.djId=?";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0,id);
		List<Object> list = null;
		list = query.list();
		return list;
	}
	/***
	 *通用方法    执行sql语句
	 */
	public boolean executeSQL(String sql) throws Exception{
		try {
			this.getJdbcTemplate().execute(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		}
	}
	
	/***
	 *通用方法    执行sql语句 赋值
	 */
	public boolean executeSqlValues(String sqlTxt, String[] sqlParamStr)
			throws Exception {
		boolean info = false;
		PreparedStatement pstmt = this.getJdbcTemplate().getDataSource().getConnection().prepareStatement(sqlTxt);
		try {
			for (int i = 1; i <= sqlParamStr.length; i++) {
				pstmt.setString(i, sqlParamStr[i-1]);
			}
			pstmt.execute();
			info = true;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return info;
	}
	
	
	/***
	 *通用方法    更新表中数据  sql语句 赋值
	 */
	public boolean executeUpdateSql(String sqlText, String[] valuesArr,String[] whereValue)
			throws Exception {
		boolean info = false;
		PreparedStatement pstmt = this.getJdbcTemplate().getDataSource().getConnection().prepareStatement(sqlText);
		try {
			int len = valuesArr.length;
			for (int i = 1; i <= len; i++) {
				pstmt.setString(i, valuesArr[i-1]);
			}
			for (int j = 1; j <= whereValue.length; j++) {
				pstmt.setString(j + len, whereValue[j-1]);
			}
			pstmt.execute();
			info = true;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return info;
	}
	
	
	/*****
	 *通用方法    执行存储过程
	 *procName PKG_ONIX_CREATOR.UP_CONVERT_ONIX21(i_root_value VARCHAR2,count Number, bC varchar2)
	 *Config   [   A+B+C ] 根据 ‘+’ 号插入数据
	 */
	public boolean executeProc(String procName,String config,int dbType){
		try {
			String callP = procName.split("\\(")[0];
			String[] callName = procName.split(",");
			String[] confS = config.split("\\+");
			if (callName.length != confS.length) {
				System.out.println("参数不匹配");
			}else{
				StringBuffer orclProcName = new StringBuffer("{call "+callP+"(");
				for (int i = 0; i < confS.length; i++) {
					orclProcName.append("?");
					if (i != confS.length - 1) {
						orclProcName.append(",");
					}
				}
				orclProcName.append(")}");
				Connection conn = this.getJdbcTemplate().getDataSource().getConnection();
				CallableStatement cs = conn.prepareCall(orclProcName.toString());
				//分别给形参赋值
				for (int i = 0; i < confS.length; i++) {
					cs.setString(i, confS[i]);
				}
				cs.execute();
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		}
	}
	/***
	 * 获取sequence 的值
	 * @param sequenceName
	 * @return
	 */
    protected Long getSequenceLongValue(String sequenceName) {  
        String sql = "select " + sequenceName + ".nextval from dual";  
        Long value = getJdbcTemplate().queryForLong(sql);  
        System.out.println("读取Sequence: sql = " + sql);  
        System.out.println("新生成的id = " + value);  
        return value;  
    }  
    
    /***
	 * 获取Map key 的 value 的值
	 * @param sequenceName
	 * @return
	 */
    protected String getPkStr(String sql) {  
        String value = "";
        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql);
        if (list!=null){
        	for (Iterator ite = list.get(0).entrySet().iterator(); ite.hasNext();) {
        		Entry<String,Object> entry = (Entry<String, Object>) ite.next();
        		value = (String) entry.getValue();
        	}
		}
        return value;  
    }  
    
	public String formatDataSql(String dsid,String sqlText, String uid) throws Exception {
		StringBuffer tmpBuf = new StringBuffer(" select ");
		try {
			String sql = "select NAME,IS_MULTI_CLNS,MULTI_CLNS_TABLE,MULTI_CLNS_FK FROM SYS_COLUMNS "+
			" WHERE DS_ID = '"+dsid+"' AND NAME NOT IN (select uc.column_name from sys_right_user_columns uc where uc.ds_id ='"+dsid+"' and uc.o_id = '"+uid+"' "+  
			" union select gc.column_name from sys_right_group_columns gc where gc.group_id in "+
			" (select g.group_id from sys_right_map g where g.o_id = '"+uid+"') and gc.ds_id = '"+dsid+"') AND ISSHOW = 'T' AND JAVA_PROPERTY_RECORDED IS NOT NULL "+
			" ORDER BY ORDER_NUM_ADD" ;
			List<Map<String, Object>> cliList = this.getJdbcTemplate().queryForList(sql);
			Map<String, Object> tmpCln = new HashMap<String, Object>();
			Object tmpObj = null;
			Object isMulti = null;
			for (int i = 0; i < cliList.size(); i++) {
				tmpCln = cliList.get(i);
				tmpObj = tmpCln.get("NAME");
				isMulti = tmpCln.get("IS_MULTI_CLNS");
				if (isMulti != null && "T".equals(isMulti.toString()) ) {
						// pk_sys_ui.uf_parse_ids('ds_id','cln_name',fk_value) 
					tmpBuf.append("pk_sys_ui.uf_parse_ids('"+dsid+"','"+tmpObj.toString()+"',"+tmpObj.toString()+") "+tmpObj.toString()+" ");
				}else{
					tmpBuf.append(tmpObj);
				}
				if (i < cliList.size() - 1) {
					tmpBuf.append(",");
				}
			}
			tmpBuf.append(" from ("+sqlText +") ttmp ");
			System.out.println("SysBaseDao #L.230# "+tmpBuf.toString());
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("读取数据，字段可能不存在["+e.getMessage()+"]，请检查Sys_columns.NAME中的字段和表中的sql是否一致");
		}
		return tmpBuf.toString();
	}
	///////////////////////////////////////////////////////////////////////////////////////////////////
	/**
	 * 根据sql 返回一个string
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public String execSqlForString(String sql) throws Exception {
		String strValue = "";
		try {
			strValue = this.getJdbcTemplate().queryForObject(sql,String.class);
			if(!"".equals(strValue)){
				return strValue;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strValue;
	}
	/**
	 * 根据sql 查询相应的dzSource
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public SysDzSource sqlForList(String sql) throws Exception {
		SysDzSource dzSource = new SysDzSource();
		try {
			List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
			if (list == null) {
				return null;
			}
			if (list.size() > 1) {
				throw new RuntimeException("查询时，出现两条对照信息，请检查！");
			}
			Map<String, Object> valueMap = list.get(0);
			if (valueMap.get("SQL_TEXT") == null ||valueMap.get("SHOW_NAME")==null ||valueMap.get("SHOW_PK")==null) {
				throw new RuntimeException("sqlText或者showName或者showPk为Null");
			}
			dzSource.setSqlText(valueMap.get("SQL_TEXT").toString());
			dzSource.setShowName(valueMap.get("SHOW_NAME").toString());
			dzSource.setShowPk(valueMap.get("SHOW_PK").toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dzSource;
	}
	
	/**
	 * 根据dz_id返回SysDzSource
	 * @param dzId
	 * @return
	 * @throws Exception
	 */
	public SysDzSource loadDzSource(String dzId) throws Exception {
		return (SysDzSource) this.getSessionFactory().getCurrentSession().load(SysDzSource.class, dzId);
	}
	/**
	 * 一对多
	 * @param dsid
	 * @param clnName
	 * @return
	 * @throws Exception
	 */
	public SysColumns queryColumnsMul(String dsid, String clnName) throws Exception {
		String sql = "select MULTI_CLNS_TABLE," +
		"MULTI_CLNS_FK," +
		"MULTI_CLNS_VALUE," +
		"MULTI_DZ_DS_ID,MULTI_SAME_DS_ID,EDITYPE from sys_columns " +
		" where ds_id = '"+dsid+"' and name = '"+clnName+"'";
		List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
		SysColumns tmpColumns = new SysColumns();
		if(list != null && !list.isEmpty()){
			tmpColumns.setMultiClnsTable(list.get(0).get("MULTI_CLNS_TABLE").toString());
			tmpColumns.setMultiClnsFk(list.get(0).get("MULTI_CLNS_FK").toString());
			tmpColumns.setMultiClnsDValue(list.get(0).get("MULTI_CLNS_VALUE").toString());
			tmpColumns.setMultiDzDsId(list.get(0).get("MULTI_DZ_DS_ID").toString());
			tmpColumns.setEdiType(list.get(0).get("EDITYPE").toString());
		}
		return tmpColumns;
	}
	/**
	 * 执行sql 返回list
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List execSqlToList(String sql) throws Exception {
		List list = new ArrayList();
		try {
			list = this.getJdbcTemplate().queryForList(sql);
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public String configForSql(String config,String dsId) throws Exception{
		try {
			if(config==null){
				return null;
			}
			String[] configS = config.split("\\|\\+");
			String[] dsIds = dsId.split(";");
			
			StringBuffer sqlBuffer = new StringBuffer();
			
			String[] tmpValue2;
			String tmpStr = "";  //缓存语句
			String tmpStr2 = ""; //缓存语句2
			String flagA = ""; //缓存标志
			String tmpName = "";//缓存 字段
			String tmpValue = "";  // 缓存值
			String editype = "";//缓存editype
			StringBuffer whereStr =  new StringBuffer();//缓存条件语句
			for (int i = 0; i < configS.length;i++) {
				if (configS[i] != null && !"".equals(configS[i])) {
					flagA = configS[i].substring(0, 1);
					tmpStr =configS[i].substring(2, configS[i].length());
//					*     字符串1  ：flag - ClnName - value1 -neq +
//							eq  等于   		neq 	不等于
//					 *      alike 以...开头  	likea　	以...结尾    inc包含
//					 *   数值 间隔2：	 flag - date Num - value1 - value2  +
//					 *       列表3：     flag - staId - value1;value2 +
//					 	   时间间隔4:   flag - clnName- date1 - date2 +
						if (flagA.equals("1")) {
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));
							tmpStr2 = tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());
							tmpStr = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-"));
							if("".equals(tmpStr)){
								continue;
							}
							if("eq".equals(tmpStr2)){
								sqlBuffer.append(" AND "+tmpName+"='"+tmpStr+"' ");
							}else if ("neq".equals(tmpStr2)) {
								sqlBuffer.append(" AND "+tmpName+" not in ('"+tmpStr+"') ");
							}else if ("alike".equals(tmpStr2)) {
								sqlBuffer.append(" AND "+tmpName+" like '"+tmpStr+"%' ");
							}else if ("likea".equals(tmpStr2)) {
								sqlBuffer.append(" AND "+tmpName+" like'%"+tmpStr+"' ");
							}else if ("inc".equals(tmpStr2)) {
								sqlBuffer.append(" AND "+tmpName+" like '%"+tmpStr+"%' ");
							}
						}else if (flagA.equals("2")) {
//							 *    间隔2：	   flag - clnName - Num - num2  +
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));
							if(tmpStr.lastIndexOf("-") > tmpStr.length()+1){
								tmpStr2 = tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());
								sqlBuffer.append(" AND "+tmpName+" <= "+tmpStr2+" ");
							}
							tmpStr = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-"));
							if(!"".equals(tmpStr)){
								sqlBuffer.append(" AND "+tmpName+" >= "+tmpStr+" ");
							}
						}else if (flagA.equals("3")) {
//							flag - staId - value1;value2 +
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));
							tmpStr =  tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.length());
							if("".equals(tmpStr)){
								continue;
							}
							tmpValue2 = tmpStr.split(";");
							tmpStr = "";
							for(int k = 0; k < tmpValue2.length; k ++){
								tmpStr += "'"+tmpValue2[k]+"'";
								if (k < tmpValue2.length - 1) {
									tmpStr += ",";
								}
							}
							sqlBuffer.append(" AND "+tmpName+" in ("+tmpStr+")");
						}else if (flagA.equals("4")) {
							//							时间间隔4:   flag - clnName- date1 - date2 +
							//							 input_date >  to_date('2014-5-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
							//							SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
							SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));
							String tmpStr3 = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-"));   //第一个时间框内的值
							String tmpStr21 = tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());  //第二个时间框内的值
							if (!"".equals(tmpStr3)&&!"".equals(tmpStr21)&&tmpStr3.equals(tmpStr21)) {  //两个框内都有值,，并且两个值相等
								String jqDate=df.format(new Date(Long.parseLong(tmpStr3)));
								sqlBuffer.append(" AND to_char("+ tmpName+" ,'yyyy-MM-dd')  = '"+jqDate+"'");
							}else{
								if(!"".equals(tmpStr3)){
									sqlBuffer.append(" AND to_char("+ tmpName +",'yyyy-MM-dd') >= '"+df.format(new Date(Long.parseLong(tmpStr3)))+"'");
								}
								if(!"".equals(tmpStr21)){
									sqlBuffer.append(" AND to_char("+ tmpName +",'yyyy-MM-dd') <= '"+df.format(new Date(Long.parseLong(tmpStr21)))+"'");
								}
							}
						}else if (flagA.equals("5")) {
							//5 flag - ClnName - editype - value1 -neq |+
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));      
							tmpStr2 = tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());
							tmpStr = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-"));
							editype = tmpStr.substring(0,tmpStr.indexOf("-"));
							tmpStr = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.length());
							
							if("".equals(tmpStr)){
								continue;
							}
							String ediSql = "select c.editype_add from sys_columns c where c.name='"+tmpName+"' and c.ds_id ='"+dsIds[0]+"'";
							String ediType = execSqlForString(ediSql);
							String sqlForText ="select sql_text,show_pk,show_name from sys_dz_source c where c.dz_id ='"+ediType+"'";
							SysDzSource dzSource =(SysDzSource)sqlForList(sqlForText);
							String showName = dzSource.getShowName();
							String sqlText = dzSource.getSqlText();
							whereStr.append(" where 1=1 ");
							if("eq".equals(tmpStr2)){
								whereStr.append(" AND "+showName+"='"+tmpStr+"' ");
							}else if ("neq".equals(tmpStr2)) {
								whereStr.append(" AND "+showName+" not in ('"+tmpStr+"') ");
							}else if ("alike".equals(tmpStr2)) {
								whereStr.append(" AND "+showName+" like '"+tmpStr+"%' ");
							}else if ("likea".equals(tmpStr2)) {
								whereStr.append(" AND "+showName+" like'%"+tmpStr+"' ");
							}else if ("inc".equals(tmpStr2)) {
								whereStr.append(" AND "+showName+" like '%"+tmpStr+"%' ");
							}
							StringBuffer allSql = new StringBuffer(sqlText);
							List<Map<String,String>> idList = execSqlToList(" select  "+ dzSource.getShowPk() +" from ("+ allSql.toString()+") A "+whereStr);
							if(idList!=null&&idList.size()>0){
								String textWhere = " and "+tmpName ;
								for (int j = 0; j < idList.size(); j++) {
									textWhere += " like '%"+idList.get(j).get(dzSource.getShowPk())+"%'";
									if(j<(idList.size()-1)){
										textWhere +=" or "+tmpName;
									}
								}
								sqlBuffer.append(textWhere);
							}else{
								String textWhere = " and 0=1" ;
								sqlBuffer.append(textWhere);
							}
						}else{
							throw new RuntimeException("查询条件传入有误,没有按照相关规则,请联系开发调试");
						}
				}
			}
			return sqlBuffer.toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
	
	/**
	 * 前台传入config，拆解变成where条件sql
	 * @param config
	 * @return
	 * @throws Exception
	 */
	public String configTOSql(String config,String dsId) throws Exception{
		try {
			if(config==null){
				return null;
			}
			StringBuffer sqlBuffer = new StringBuffer();
			String[] tmpValue2;
			String tmpStr = "";  //缓存语句
			String tmpStr2 = ""; //缓存语句2
			String flagA = ""; //缓存标志
			String tmpName = "";//缓存 字段
			String tmpValue = "";  // 缓存值
			String editype = "";//缓存editype
			if(config!=null&&config.trim()!=""){
				String[] configS = config.split("\\|\\+");
				StringBuffer whereStr =  new StringBuffer();//缓存条件语句
				for (int i = 0; i < configS.length;i++) {
					if (configS[i] != null && !"".equals(configS[i])) {
						flagA = configS[i].substring(0, 1);
						tmpStr =configS[i].substring(2, configS[i].length());
						//					*     字符串1  ：flag - ClnName - value1 -neq +
						//							eq  等于   		neq 	不等于
						//					 *      alike 以...开头  	likea　	以...结尾    inc包含
						//					 *   数值 间隔2：	 flag - date Num - value1 - value2  +
						//					 *       列表3：     flag - staId - value1;value2 +
						//					 	   时间间隔4:   flag - clnName- date1 - date2 +
						if (flagA.equals("1")) {
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));
							tmpStr2 = tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());
							tmpStr = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-")).trim();
							if("".equals(tmpStr)){
								continue;
							}
							//获取input框传入的参数
							String parm=tmpStr.toString().toUpperCase();
							String [] parms=parm.split(";");
							//遍历参数       对出版社简sql条件进行添加
							for(String p:parms){
								if("eq".equals(tmpStr2)){
									if(p.matches("^[^a-zA-Z]*$")){
										sqlBuffer.append(" AND "+tmpName+" = '"+p+"' ");
									}else {
										sqlBuffer.append(" AND upper(getch2spell(Nvl("+tmpName+",' '))) like getch2spell('"+p+"') ");
									}
									
									//	sqlBuffer.append(" AND (("+tmpName+"='"+p+"') OR (upper(getch2spell(Nvl("+tmpName+",' ')))= getch2spell('"+p+"')))  ");
								}else if ("neq".equals(tmpStr2)) {
									if(p.matches("^[^a-zA-Z]*$")){
										sqlBuffer.append(" AND ("+tmpName+" not in ('"+p+"'))  ");
									}else {
										sqlBuffer.append(" AND  (upper(getch2spell(Nvl("+tmpName+",' ')))  not in getch2spell('"+p+"')) ");
									}
									//sqlBuffer.append(" AND (("+tmpName+" not in ('"+p+"')) AND  upper((getch2spell(Nvl("+tmpName+",' ')))  not in getch2spell('"+p+"')))");
								}else if("alike".equals(tmpStr2)) {
									if(p.matches("^[^a-zA-Z]*$")){
										sqlBuffer.append(" AND "+tmpName+" like '"+p+"%' ");
									}else {
										sqlBuffer.append(" AND upper(getch2spell(Nvl("+tmpName+",' '))) like getch2spell('"+p+"%')  ");
									}
									//	sqlBuffer.append(" AND upper(getch2spell(Nvl("+tmpName+",' '))) like getch2spell('"+p+"%') ");
								}else if ("likea".equals(tmpStr2)) {
									if(p.matches("^[^a-zA-Z]*$")){
										sqlBuffer.append(" AND "+tmpName+" like '%"+p+"'");
									}else {
										sqlBuffer.append("  AND upper(getch2spell(Nvl("+tmpName+",' '))) like getch2spell('%"+p+"')  ");
									}
								
								//sqlBuffer.append(" AND upper(getch2spell(Nvl("+tmpName+",' '))) like getch2spell('%"+p+"') ");
							   }else if ("inc".equals(tmpStr2)) {
						            //判断传入的参数不是英文
									if(p.matches("^[^a-zA-Z]*$")){
										sqlBuffer.append(" AND "+tmpName+" like '%"+p+"%' ");
									}else {
										sqlBuffer.append(" AND upper(getch2spell(Nvl("+tmpName+",' '))) like getch2spell('%"+p+"%') ");
									}
								}
							}
						}else if (flagA.equals("2")) {
							//							 *    间隔2：	   flag - clnName - Num - num2  +
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));
							if(tmpStr.lastIndexOf("-") > tmpStr.length()+1){
								tmpStr2 = tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());
								sqlBuffer.append(" AND "+tmpName+" <= "+tmpStr2+" ");
							}
							tmpStr = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-"));
							if(!"".equals(tmpStr)){
								sqlBuffer.append(" AND "+tmpName+" >= "+tmpStr+" ");
							}
						}else if (flagA.equals("3")) {   //3-STOCK_ID-GOM1K00000|+        3-PRINT_MAN-22378EADC22087536F54752CD5FD1CE6|+
							//							flag - staId - value1;value2 +       
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));
							tmpStr =  tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.length());
							if("".equals(tmpStr)){
								continue;
							}
							tmpValue2 = tmpStr.split(";");
							tmpStr = "";
							for(int k = 0; k < tmpValue2.length; k ++){
								tmpStr += "'"+tmpValue2[k]+"'";
								if (k < tmpValue2.length - 1) {
									tmpStr += ",";
								}
							}
							sqlBuffer.append(" AND "+tmpName+" in ("+tmpStr+")");
						}else if (flagA.equals("4")) {
//							时间间隔4:   flag - clnName- date1 - date2 +
							//							 input_date >  to_date('2014-5-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
							//							SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
							SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
							tmpName = tmpStr.substring(0,tmpStr.indexOf("-"));
							String tmpStr3 = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-"));   //第一个时间框内的值
							String tmpStr21 = tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());  //第二个时间框内的值
							if (!"".equals(tmpStr3)&&!"".equals(tmpStr21)&&tmpStr3.equals(tmpStr21)) {  //两个框内都有值,，并且两个值相等
								String jqDate=df.format(new Date(Long.parseLong(tmpStr3)));
								sqlBuffer.append(" AND to_char("+ tmpName+" ,'yyyy-MM-dd')  = '"+jqDate+"'");
							}else{
								if(!"".equals(tmpStr3)){
									sqlBuffer.append(" AND to_char("+ tmpName +",'yyyy-MM-dd') >= '"+df.format(new Date(Long.parseLong(tmpStr3)))+"'");
								}
								if(!"".equals(tmpStr21)){
									sqlBuffer.append(" AND to_char("+ tmpName +",'yyyy-MM-dd') <= '"+df.format(new Date(Long.parseLong(tmpStr21)))+"'");
								}
							}
						}else if (flagA.equals("5")) {
							//5 flag - ClnName - editype - value1 -neq |+
							if("".equals(tmpStr)){
								continue;
							}
							String colnName=tmpStr.substring(0,tmpStr.indexOf("-"));      //列名
							String operatorFlag=tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());  //关系运算符
							tmpStr =tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-")); //dzId-value    
							
//							
							
							String dzId = tmpStr.substring(0,tmpStr.indexOf("-"));   //编码对照信息的dzId
							String valueStr= tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.length());   //输入的查询条件的字符串的
							String[] valArr1=valueStr.split(";");  //前台输入的谐音数组 NAME = CL;JK;KL;
//							List<String> vals=new ArrayList<String>();  //谐音数组过滤后过控制的集合的值
//							for (int j = 0; j < valArr1.length; j++) {
//								if(valArr1[j]!=null&&valArr1[j]!=""){
//									vals.add(valArr1[j]);
//								}
//							}
							String sqlForText ="select sql_text,show_pk,show_name from sys_dz_source c where c.dz_id ='"+dzId+"'";
							SysDzSource dzSource =this.sqlForList(sqlForText);
							String showName = dzSource.getShowName();
							String showPk=dzSource.getShowPk();
							String sqlText = dzSource.getSqlText();
							
//							StringBuffer configSql = new StringBuffer();
//							configSql.append(" AND ");
							
							String sqlConfig =" AND";
							for(int n=0;n<valArr1.length;n++){
								String p = valArr1[n];
								if("eq".equals(operatorFlag)){
									if(p.matches("^[a-zA-Z]*$")){
										sqlConfig +="  upper(getch2spell(Nvl("+showName+",' ')))= getch2spell(Nvl('"+p.trim().toUpperCase()+"',''))  ";
									}else {
										sqlConfig +=" ("+showName+"='"+p+"')";
									}
								}else if ("neq".equals(operatorFlag)) {
									   sqlConfig +="("+showName+" not in ('"+p+"')) ";
								}else if ("alike".equals(operatorFlag)) {
									if(p.matches("^[a-zA-Z]*$")){
										sqlConfig +=" upper(getch2spell(Nvl("+showName+",' '))) like getch2spell('"+p.trim().toUpperCase()+"%') ";
									}else {
										sqlConfig +=" nvl("+showName+",' ') like '"+p+"%'";
									}
								}else if ("likea".equals(operatorFlag)) {
									if(p.matches("^[a-zA-Z]*$")){
										sqlConfig +="  upper(getch2spell(Nvl("+showName+",' '))) like getch2spell('%"+p.trim().toUpperCase()+"')";
									}else {
										sqlConfig +=" Nvl("+showName+",' ') like '%"+p+"'";
									}
								}else if ("inc".equals(operatorFlag)) {
									if(p.matches("^[a-zA-Z]*$")){
								        sqlConfig +=" upper(getch2spell(Nvl("+showName+",' '))) like getch2spell('%"+p.trim().toUpperCase()+"%') ";
									}else {
										sqlConfig +=" Nvl("+showName+",' ') like '%"+p+"%'";
									}
								}
								if (n<valArr1.length-1) {
									sqlConfig = sqlConfig +" OR";
								}
								//拼接条件
//								configSql.append(getSql(vals,showName,sqlConfig));
							}
							//得到前台输入的条件的编码的集合
//							String tmpSql = " select  "+showPk +" from ("+ sqlText+") A where 1=1 "+configSql;
							String tmpSql = " select  "+showPk +" from ("+ sqlText+") A where 1=1 "+sqlConfig;
							List<Map<String,String>> idList = this.execSqlToList(tmpSql);
							
							StringBuffer likestr = new StringBuffer();
							if (idList != null && !idList.isEmpty()) {
								for (int j = 0; j < idList.size(); j++) {
									likestr.append( colnName + " like '%"+idList.get(j).get(dzSource.getShowPk())+"%'");
									if(j<(idList.size()-1)){
										likestr.append(" or ");
									}
								}                                                                                                                                                          
								sqlBuffer.append(" AND ("+ likestr+") ");
							}else {
								sqlBuffer.append(" AND (0=1) ");
							}
						}else if (flagA.equals("6")) {	//一对多根据ID查
							//							flag - clnName - dsid - value1;value2 +
							//根据dsid 和 name 查找对应的表   及对应的字段、
							String clnName = tmpStr.substring(0, tmpStr.indexOf("-"));
							tmpStr = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.length());
							String dsid = tmpStr.substring(0,tmpStr.indexOf("-"));
							//查找出包含的所有的字段
							//只查询mul的四个字段  
							//MULTI_CLNS_TABLE,MULTI_CLNS_FK,MULTI_CLNS_VALUE,MULTI_DZ_DS_ID
							SysColumns tmpOnly4P = this.queryColumnsMul(dsid,clnName);
							//select distinct sin_id where mul_id in (ids);
							//								sql语句 本字段   包含在这里面

							String[] idsArr = tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.length()).split(";");
							StringBuffer tmpIds = new StringBuffer();
//							V1.0.1
//							for(int k = 0; k < idsArr.length; k ++){
//								tmpIds.append("'"+idsArr[k]+"'");
//								if (k < idsArr.length - 1) {
//									tmpIds.append(",");
//								}
//							}
//							v2.0
							for(int k = 0; k < idsArr.length; k ++){
								tmpIds.append(clnName + " LIKE '%"+idsArr[k]+"%' ");
								if (k < idsArr.length - 1) {
									tmpIds.append(" OR ");
								}
							}
							
							//			" AND SIN_ID IN (select distinct sin_id where mul_id in (ids))
							tmpStr = " select distinct "+ tmpOnly4P.getMultiClnsFk() 
							+" from " + tmpOnly4P.getMultiClnsTable()
							+" where " + tmpOnly4P.getMultiClnsDValue()+" in ("
							+tmpIds+")";
							
//							sqlBuffer.append(" AND "+clnName+" in ("+tmpStr+")"); //v1.0.1
							sqlBuffer.append(" AND ("+tmpIds+")"); //v2.0.0
							
						}
						//一对多的模糊查询(flag - clnName - dsid - value1;value2 -neq +)
						else if (flagA.equals("7")) {	
							String clnName = tmpStr.substring(0, tmpStr.indexOf("-")); //列名
							String operatorFlag=tmpStr.substring(tmpStr.lastIndexOf("-")+1,tmpStr.length());  //关系运算符
							tmpStr =tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.lastIndexOf("-")); //dzId-value
							String dzId = tmpStr.substring(0,tmpStr.indexOf("-"));   //编码对照信息的dzId
							String valueStr= tmpStr.substring(tmpStr.indexOf("-")+1,tmpStr.length());   //输入查询条件字符串
							String[] valArr1=valueStr.split(";");  //字符串分割的谐音数组
							List<String> vals=new ArrayList<String>();  //谐音数组过滤后过控制的集合的值
							for (int j = 0; j < valArr1.length; j++) {
								if(valArr1[j]!=null&&valArr1[j]!=""){
									vals.add(valArr1[j]);
								}
							}
							//根据dsId，和列名查询该列一对多的的配置信息
							SysColumns mulObj = this.queryColumnsMul(dsId,clnName);
							SysDzSource dzSource = this.loadDzSource(dzId);
							if (dzSource == null ) {
								throw new RuntimeException("找不到对照信息["+mulObj.getEditypeQr()+"]用于条件查询");
							}
							String showName = dzSource.getShowName();
							String showPk=dzSource.getShowPk();
							StringBuffer sqlText = new StringBuffer(dzSource.getSqlText());
							
//							StringBuffer configSql = new StringBuffer();
//							configSql.append(" AND   ");
							
							String sqlConfig =" AND";
							for(int n=0;n<valArr1.length;n++){
								String p = valArr1[n];
								if("eq".equals(operatorFlag)){
									if(p.matches("^[a-zA-Z]*$")){
										sqlConfig +="  upper(getch2spell(Nvl("+showName+",' ')))= getch2spell(Nvl('"+p.trim().toUpperCase()+"',''))  ";
									}else {
										sqlConfig +=" ("+showName+"='"+p+"')";
									}
								}else if ("neq".equals(operatorFlag)) {
									   sqlConfig +="("+showName+" not in ('"+p+"')) ";
								}else if ("alike".equals(operatorFlag)) {
									if(p.matches("^[a-zA-Z]*$")){
										sqlConfig +=" upper(getch2spell(Nvl("+showName+",' '))) like getch2spell('"+p.trim().toUpperCase()+"%') ";
									}else {
										sqlConfig +=" nvl("+showName+",' ') like '"+p+"%'";
									}
								}else if ("likea".equals(operatorFlag)) {
									if(p.matches("^[a-zA-Z]*$")){
										sqlConfig +="  upper(getch2spell(Nvl("+showName+",' '))) like getch2spell('%"+p.trim().toUpperCase()+"')";
									}else {
										sqlConfig +=" Nvl("+showName+",' ') like '%"+p+"'";
									}
								}else if ("inc".equals(operatorFlag)) {
									if(p.matches("^[a-zA-Z]*$")){
								        sqlConfig +=" upper(getch2spell(Nvl("+showName+",' '))) like getch2spell('%"+p.trim().toUpperCase()+"%') ";
									}else {
										sqlConfig +=" Nvl("+showName+",' ') like '%"+p+"%'";
									}
								}
								if (n<valArr1.length-1) {
									sqlConfig = sqlConfig +" OR";
								}
							}
							String tmpSql = " select  "+ showPk +" from ("+ sqlText+") A where 1=1 "+sqlConfig;
							//获取编码的集合
							List<Map<String,String>> idList = this.execSqlToList(tmpSql);
							StringBuffer likestr=new StringBuffer();
							if (idList != null && !idList.isEmpty()) {
								for (int j = 0; j < idList.size(); j++) {
									likestr.append( clnName + " like '%"+idList.get(j).get(showPk)+"%'");
									if(j<(idList.size()-1)){
										likestr.append(" or ");
									}
								}                                                                                                                                                          
								sqlBuffer.append(" AND ("+ likestr+") ");
							}else {
								sqlBuffer.append(" AND (0=1) ");
							}
						}else{
							throw new RuntimeException("查询条件传入有误,没有按照相关规则,请联系开发调试");
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
}
